package wiki.tools.vidition.droid.util;

import java.util.ArrayList;

import wiki.tools.vidition.droid.util.bean.BookMarkBean;
import wiki.tools.vidition.droid.util.bean.NoteBookBean;
import wiki.tools.vidition.droid.util.bean.RecommednBean;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DbHelper {

	private static final String NOTE_NAME = "notebook";
	private static final String NOTE_TITLE = "title";
	private static final String NOTE_BODY = "body";
	private static final String NOTE_COUNT = "count";
	private static final String NOTE_TIME = "time";
	private static final String DATABASE_CREATE_NOTE = "create table notebook (title text primary key, body text, count integer, time text not null);";

	private static final String BOOK_MARK_NAME = "bookmark";
	private static final String BOOK_MARK_TITLE = "title";
	private static final String BOOK_MARK_BODY = "body";
	private static final String DATABASE_CREATE_BOOK_MARK = "create table bookmark (title text primary key, body text not null);";

	private static final String HIGH_NAME = "high";
	private static final String HIGH_TITLE = "title";
	private static final String HIGH_TIME = "time";
	private static final String HIGH_COUNT = "count";
	private static final String DATABASE_CREATE_HIGH = "create table high (title text primary key, count integer, time text not null);";

	private static final String DATABASE_NAME = "wiki";
	private static final int DATABASE_VERSION = 1;

	private DataBase help;
	private SQLiteDatabase mDb;
	private Context mContext;

	public DbHelper(Context c) {
		this.mContext = c;
	}

	private class DataBase extends SQLiteOpenHelper {

		public DataBase(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE_NOTE);
			db.execSQL(DATABASE_CREATE_BOOK_MARK);
			db.execSQL(DATABASE_CREATE_HIGH);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS notebook");
			db.execSQL("DROP TABLE IF EXISTS bookmark");
			db.execSQL("DROP TABLE IF EXISTS high");
			onCreate(db);
		}
	}

	public DbHelper open() throws SQLiteException {

		help = new DataBase(mContext);
		mDb = help.getWritableDatabase();
		return this;
	}

	public void close() {
		help.close();
	}

	public long createBookMark(String title, String body) {
		ContentValues newbookmark = new ContentValues();
		newbookmark.put(BOOK_MARK_TITLE, title);
		newbookmark.put(BOOK_MARK_BODY, body);
		return mDb.insert(BOOK_MARK_NAME, null, newbookmark);
	}

	public boolean insertBookMark(BookMarkBean bean) {
		return createBookMark(bean.title, bean.body) == -1 ? false : true;
	}

	public long createNote(String title, String body, int count, String time) {
		ContentValues values = new ContentValues();
		values.put(NOTE_TITLE, title);
		values.put(NOTE_BODY, body);
		values.put(NOTE_COUNT, count);
		values.put(NOTE_TIME, time);
		return mDb.insert(NOTE_NAME, null, values);
	}

	public long createHigh(String title, int count, String time) {
		ContentValues values = new ContentValues();
		values.put(HIGH_TITLE, title);
		values.put(HIGH_COUNT, count);
		values.put(HIGH_TIME, time);
		return mDb.insert(HIGH_NAME, null, values);
	}

	public boolean deleteNote(String title) {
		mDb.delete(NOTE_NAME, NOTE_TITLE + "='" + title + "'", null);
		return true;
	}

	public boolean deleteHigh(String title) {
		return mDb.delete(HIGH_NAME, HIGH_TITLE + "='" + title + "'", null) > 0;
	}

	public void deleteAllDataBookMark() {
		mDb.execSQL("delete from bookmark;");
	}

	public void deleteAllHigh() {
		mDb.execSQL("delete from " + HIGH_NAME + ";");
	}

	public void deleteAllDataNoteBook() {
		mDb.execSQL("delete from notebook;");
	}

	public boolean deleteBookMark(String title) {
		return mDb.delete(BOOK_MARK_NAME, BOOK_MARK_TITLE + "='" + title + "'",
				null) > 0;
	}

	private Cursor fetchAllHigh() {
		return mDb.query(HIGH_NAME, new String[] { HIGH_TITLE, HIGH_COUNT,
				HIGH_TIME }, null, null, null, null, null);
	}

	private Cursor fetchAllNotes() {
		return mDb.query(NOTE_NAME, new String[] { NOTE_TITLE, NOTE_BODY,
				NOTE_COUNT, NOTE_TIME }, null, null, null, null, null);
	}

	private Cursor fetchAllBookMark() {
		return mDb.query(BOOK_MARK_NAME, new String[] { BOOK_MARK_TITLE,
				BOOK_MARK_BODY }, null, null, null, null, null);
	}

	private Cursor fetchNote(String title) {
		Cursor mCursor = mDb.query(NOTE_NAME, new String[] { NOTE_TITLE,
				NOTE_BODY, NOTE_COUNT, NOTE_TIME }, NOTE_TITLE + "='" + title
				+ "'", null, null, null, null);
		return mCursor;
	}

	private Cursor fetchHigh(String title) {
		return mDb.query(HIGH_NAME, new String[] { HIGH_TITLE, HIGH_COUNT,
				HIGH_TIME }, HIGH_TITLE + "='" + title + "'", null, null, null,
				null);
	}

	private Cursor fetchBookMark(String title) {
		Cursor mCursor = mDb.query(BOOK_MARK_NAME, new String[] {
				BOOK_MARK_TITLE, BOOK_MARK_BODY }, BOOK_MARK_TITLE + "='"
				+ title + "'", null, null, null, null);
		return mCursor;
	}

	private boolean updateNote(String title, String body, int count, String time) {

		ContentValues values = new ContentValues();
		values.put(NOTE_TITLE, title);
		values.put(NOTE_BODY, body);
		values.put(NOTE_COUNT, count);
		values.put(NOTE_TIME, time);
		return mDb.update(NOTE_NAME, values, NOTE_TITLE + "='" + title + "'",
				null) > 0;
	}

	private boolean updateBookMark(String title, String body) {
		ContentValues values = new ContentValues();
		values.put(BOOK_MARK_TITLE, title);
		values.put(BOOK_MARK_BODY, body);
		return mDb.update(BOOK_MARK_NAME, values, BOOK_MARK_TITLE + "='"
				+ title + "'", null) > 0;
	}

	private boolean updateHigh(String title, int count, String time) {
		ContentValues values = new ContentValues();
		values.put(HIGH_TITLE, title);
		values.put(HIGH_COUNT, count);
		values.put(HIGH_TIME, time);
		return mDb.update(HIGH_NAME, values, HIGH_TITLE + "='" + title + "'",
				null) > 0;
	}

	public boolean updateOneHigh(RecommednBean bean) {
		return updateHigh(bean.title, bean.count, bean.time);
	}

	public boolean updateOneNote(NoteBookBean bean) {
		return updateNote(bean.title, bean.body, bean.count, bean.time);
	}

	public boolean updateOneBookMark(BookMarkBean bean) {
		return updateBookMark(bean.title, bean.body);
	}

	public NoteBookBean getOneNote(String title) {
		NoteBookBean bean = null;
		Cursor cursor = fetchNote(title);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			bean = new NoteBookBean();
			bean.title = cursor.getString(0);
			bean.body = cursor.getString(1);
			bean.count = cursor.getInt(2);
			bean.time = cursor.getString(3);
			cursor.moveToNext();
			cursor.close();
		}

		return bean;
	}

	public BookMarkBean getOneBookMark(String title) {
		BookMarkBean bean = null;
		Cursor cursor = fetchBookMark(title);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			bean = new BookMarkBean();
			bean.title = cursor.getString(0);
			bean.body = cursor.getString(1);
			cursor.moveToNext();
		}
		cursor.close();
		return bean;
	}

	public ArrayList<BookMarkBean> getAllBookMark() {
		ArrayList<BookMarkBean> book = new ArrayList<BookMarkBean>();
		BookMarkBean bean = null;
		Cursor cursor = fetchAllBookMark();
		if (null != cursor) {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				bean = new BookMarkBean();
				bean.title = cursor.getString(0);
				bean.body = cursor.getString(1);
				book.add(bean);
				cursor.moveToNext();
			}
		}
		cursor.close();
		return book;
	}

	public ArrayList<NoteBookBean> getAllNoteBook() {
		ArrayList<NoteBookBean> note = new ArrayList<NoteBookBean>();
		NoteBookBean bean = null;
		Cursor cursor = fetchAllNotes();
		if (null != cursor) {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				bean = new NoteBookBean();
				bean.title = cursor.getString(0);
				bean.body = cursor.getString(1);
				bean.count = cursor.getInt(2);
				bean.time = cursor.getString(3);
				note.add(bean);
				cursor.moveToNext();
			}
		}
		cursor.close();
		return note;
	}

	public ArrayList<RecommednBean> getAllHigh() {
		ArrayList<RecommednBean> high = new ArrayList<RecommednBean>();
		RecommednBean bean = null;
		Cursor cursor = fetchAllHigh();
		if (null != cursor) {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				bean = new RecommednBean();
				bean.title = cursor.getString(0);
				bean.count = cursor.getInt(1);
				bean.time = cursor.getString(2);
				high.add(bean);
				cursor.moveToNext();
			}
		}
		cursor.close();
		return high;
	}

	public boolean saveNoteBook(NoteBookBean bean) {
		if (!updateOneNote(bean)) {
			if (-1 == createNote(bean.title, bean.body, bean.count, bean.time)) {
				return false;
			}
		}
		return true;
	}

	public boolean saveHigh(String title) {
		Cursor cursor = fetchHigh(title);
		RecommednBean bean = new RecommednBean();
		bean.title = title;
		bean.time = String.valueOf(System.currentTimeMillis());
		int count = 0;
		cursor.moveToFirst();
		if (cursor.moveToNext()) {
			count = cursor.getInt(1);
			bean.count = count + 1;
			updateOneHigh(bean);
		} else {
			createHigh(bean.title, bean.count, bean.time);
		}
		cursor.close();
		return true;
	}

	public boolean qureBookMark(String title) {
		if (null == getOneBookMark(title)) {
			return false;
		}
		return true;
	}
}